Selecting Databases Using USE in MySQL

The USE statement in MySQL is used to select a database as the current default database for the active session. After selecting a database, you can create tables, insert records, update data, and retrieve information without repeatedly specifying the database name before every table name. Understanding the USE statement is an important step in learning MySQL database management.

MySQL Training Course: JustAcademy MySQL Training Course

Course Demo: Register for MySQL Course Demo

1. Learning Objectives

After completing this topic, learners will be able to:

• Understand the purpose of the USE statement in MySQL.
• Learn the syntax for selecting a database.
• Display available databases using SHOW DATABASES.
• Create and select a database using CREATE DATABASE and USE.
• Check the currently selected database using SELECT DATABASE().
• Switch between multiple databases within a session.
• Access tables using fully qualified database and table names.
• Identify and fix common database-selection errors.
• Select a database using the MySQL command-line client and MySQL Workbench.

2. Introduction to Databases in MySQL

A database is an organized collection of information stored electronically. MySQL databases can contain tables, views, indexes, stored procedures, and other database objects. Each database can store information for a particular application or business requirement.

For example, a college management system might use a database to store student details, course information, examination results, and fee records.

When multiple databases exist on a MySQL server, you need to specify which database should be the default for your current session. The USE statement performs this task.

3. What Is the USE Statement?

The USE statement tells MySQL to use a specified database as the default database for subsequent statements in the current session. It does not create a database or delete existing data.

Syntax

USE database_name;

Example

USE college_db;

If the database exists and your account has the required privileges, MySQL selects college_db as the current database.

In the MySQL command-line client, a successful statement commonly produces the following message:

Database changed

Explanation of the Syntax

The selected database remains the default until another USE statement is issued or the session ends. The database name in a USE statement must be specified on a single line.

4. Displaying Available Databases

Before selecting a database, you can display the databases visible to your MySQL account by using the SHOW DATABASES statement.

Syntax

SHOW DATABASES;

Example Output

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| college_db         |
| company_db         |
+--------------------+

The actual list varies depending on your MySQL server, configuration, and account privileges. Databases that your account is not permitted to see might not appear.

Select a Database from the List

USE company_db;

This selects company_db as the default database for subsequent statements that use unqualified table names.

5. Creating and Selecting a Database

If the database you want to use does not exist, you may create it if your account has the required privileges. Creating a database and selecting a database are two separate operations.

Step 1: Create a Database

CREATE DATABASE training_db;

This statement creates a database named training_db. If it already exists, MySQL normally reports an error.

Step 2: Select the Database

USE training_db;

This statement selects the newly created database as the default database for the current session.

Step 3: Verify the Current Database

SELECT DATABASE();

Example output:

+----------------+
| DATABASE()     |
+----------------+
| training_db    |
+----------------+

The DATABASE() function returns the name of the current default database. If no database is selected, it returns NULL.

Alternative: Avoid an Error if the Database Already Exists

CREATE DATABASE IF NOT EXISTS training_db;
USE training_db;

The IF NOT EXISTS clause avoids an error when a database with the specified name already exists. You still need to execute USE to select it.

6. Working with Tables After Using USE

Once a database has been selected, you can create tables and run queries against them without repeatedly adding the database name.

Step 1: Select the Database

CREATE DATABASE IF NOT EXISTS college_db;
USE college_db;

Step 2: Create a Students Table

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    age INT,
    course VARCHAR(100)
);

The students table is created inside the currently selected college_db database.

Step 3: Insert Sample Records

INSERT INTO students
(student_id, student_name, age, course)
VALUES
(1, 'Aarav Sharma', 21, 'MySQL'),
(2, 'Priya Patel', 22, 'Data Analytics'),
(3, 'Rahul Verma', 20, 'Web Development');

Step 4: Retrieve Records

SELECT * FROM students;

Because college_db is the current database, MySQL searches for the students table within that database.

Step 5: Display the Tables

SHOW TABLES;

This statement lists the tables visible in the current database.

7. Switching Between Databases

You can change the current database at any time during a session by executing another USE statement. This is useful when working on multiple projects or managing databases for different applications.

Example

CREATE DATABASE IF NOT EXISTS college_db;
CREATE DATABASE IF NOT EXISTS company_db;

USE college_db;
SELECT DATABASE();

USE company_db;
SELECT DATABASE();

The first SELECT DATABASE() returns college_db, and the second returns company_db.

Steps for Switching Databases

1. Connect to the MySQL server.
2. Select the first database using USE.
3. Execute queries against tables in that database.
4. Run USE again with the name of the next database.
5. Verify the selection using SELECT DATABASE().
6. Continue working with tables in the newly selected database.

Important: Switching databases does not move, rename, or delete tables from the previous database. It only changes the default database for the current session.

8. Accessing Tables Without Using USE

The USE statement is convenient but not compulsory. You can access a table in another database by specifying its database name and table name explicitly. This is known as using a fully qualified table name.

Syntax

database_name.table_name

Example

SELECT * FROM college_db.students;
SELECT * FROM company_db.employees;

These queries specify exactly which database contains each table. They can be used without changing the current default database, provided the tables exist and the account has the required permissions.

Comparison

Use USE when most of your work takes place in one database. Use fully qualified names when a query needs to reference tables in different databases.

9. Selecting a Database in the MySQL Command-Line Client

The MySQL command-line client allows users to connect to a MySQL server and execute SQL statements directly.

Step 1: Connect to MySQL

mysql -u root -p

Enter the password when prompted. Replace root with your assigned MySQL username if necessary.

Step 2: View Available Databases

SHOW DATABASES;

Step 3: Select a Database

USE college_db;

Step 4: Verify the Database

SELECT DATABASE();

Alternative: Select the Database When Connecting

If you already know which database you want to use, you can specify it in the connection command.

mysql -u root -p college_db

After a successful connection, college_db is selected as the default database. Use the password prompt instead of placing a password directly in the command line.

10. Selecting a Database in MySQL Workbench

MySQL Workbench is a graphical tool used to connect to MySQL servers, execute queries, and manage database objects.

Method 1: Use an SQL Statement

1. Open MySQL Workbench.
2. Connect to your MySQL server.
3. Open a SQL editor tab.
4. Run SHOW DATABASES; to inspect available databases.
5. Execute USE college_db;.
6. Run SELECT DATABASE(); to verify the current database.

Method 2: Set the Default Schema

1. Locate the SCHEMAS panel in MySQL Workbench.
2. Find the database you want to use.
3. Depending on your Workbench version, double-click the schema or use its context menu to set it as the default schema.
4. Confirm the selected schema and execute your SQL statements.

The selected schema is commonly displayed in bold. The interface can differ between versions, but the USE statement is a direct SQL method for selecting a database in the current session.

11. Common Errors When Using USE

Example 1: Correcting a Misspelled Database Name

Incorrect statement:

USE collge_db;

If the actual database is named college_db, the misspelled name causes an unknown database error.

Correct statement:

USE college_db;

Example 2: Fixing No Database Selected

Suppose you execute the following query without selecting a database:

SELECT * FROM students;

If no default database is selected and the table name is not qualified, MySQL may return a no-database-selected error.

Solution:

USE college_db;
SELECT * FROM students;

Example 3: Verifying the Selected Database

SELECT DATABASE();
SHOW TABLES;

Use these statements to confirm the current database and list its tables before executing queries that modify data.

12. Best Practices for Using USE

• Run SHOW DATABASES to inspect databases visible to your account.
• Use the exact database name, including consistent letter casing.
• Run SELECT DATABASE() when you are unsure which database is selected.
• Remember that USE changes the default database only for the current session.
• Use fully qualified table names when accessing tables in multiple databases.
• Do not assume CREATE DATABASE automatically selects the database.
• Request the correct privileges if access is denied.
• Double-check the current database before executing UPDATE or DELETE statements.
• Follow consistent naming conventions across projects.
• Test queries in a development environment before using them in production.

13. Difference Between CREATE DATABASE and USE

Remember that creating a database and selecting a database are different operations. A database generally needs to be created only once, but you may need to select it each time you open a new MySQL session.

14. Frequently Asked Questions (FAQs)

Q1. What is the purpose of the USE statement in MySQL?

It selects a database as the default database for subsequent statements in the current session.

Q2. What is the syntax of USE?

The syntax is USE database_name;.

Q3. Does USE create a database?

No. USE selects an existing database. Use CREATE DATABASE to create one.

Q4. How can I see the databases available to my account?

Run SHOW DATABASES;. The results depend on your account privileges.

Q5. How can I check the current database?

Run SELECT DATABASE();. It returns the current database name or NULL if none is selected.

Q6. Can I switch databases without reconnecting?

Yes. Execute another USE statement with the database you want to select.

Q7. Does USE affect other MySQL sessions?

No. The current database is a session-level setting, so changing it in one session does not change the selection in another session.

Q8. Can I access a table without using USE?

Yes. Specify the database and table explicitly, such as SELECT * FROM college_db.students;, if you have the necessary privileges.

Q9. Why do I get an unknown database error?

The database may not exist, or its name may be misspelled. Run SHOW DATABASES and check the spelling.

Q10. Do I need to run USE every time I connect?

If you connect without specifying a database, you need to select one before using unqualified table names. You can also specify the database when starting the MySQL client.

Q11. Can I access tables from two databases in the same query?

Yes. You can use fully qualified table names to reference tables from different databases, provided you have the required privileges.

Q12. Does USE delete tables from the previous database?

No. It only changes the current default database. The previous database and its tables remain unchanged.

15. Practice Questions

1. What is the purpose of the USE statement?
2. Write the syntax for selecting a database named sales_db.
3. Which command displays databases visible to your MySQL account?
4. How do you check the currently selected database?
5. Explain the difference between CREATE DATABASE and USE.
6. What happens when you try to select a database that does not exist?
7. How can you retrieve records from a table without selecting its database first?
8. Does switching databases delete tables from the previously selected database?
9. How can you select a database when starting the MySQL command-line client?
10. How would you troubleshoot an ERROR 1046 no-database-selected message?
11. What is a fully qualified table name? Give an example.
12. Why should you verify the current database before executing a data modification query?

Practical Exercise

Complete the following exercise in your MySQL environment:

1. Create a database named practice_db.
2. Select it using the USE statement.
3. Verify the current database using SELECT DATABASE().
4. Create a table named employees with employee_id, employee_name, and department columns.
5. Insert three employee records.
6. Display the records using SELECT.
7. Create or select a second test database and switch to it.
8. Verify the database change using SELECT DATABASE().
9. Switch back to practice_db and display its tables using SHOW TABLES.
10. Retrieve employee records using the fully qualified table name.

Sample Solution

CREATE DATABASE IF NOT EXISTS practice_db;
USE practice_db;

SELECT DATABASE();

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(100)
);

INSERT INTO employees
(employee_id, employee_name, department)
VALUES
(1, 'Amit Shah', 'IT'),
(2, 'Neha Singh', 'HR'),
(3, 'Karan Mehta', 'Finance');

SELECT * FROM employees;
SHOW TABLES;

SELECT * FROM practice_db.employees;

16. Quick Revision

• USE database_name; selects the current default database.
• SHOW DATABASES; lists databases visible to your account.
• SELECT DATABASE(); returns the current database name.
• CREATE DATABASE database_name; creates a database.
• SHOW TABLES; lists tables in the current database.
• database_name.table_name identifies a table in a specific database.
• USE changes the default database for the current session and does not delete or move data.

17. Conclusion

The USE statement is a fundamental MySQL command that selects the database on which you want to work. It simplifies SQL queries by allowing you to refer to tables without repeating the database name. By combining USE with SHOW DATABASES, SELECT DATABASE(), and fully qualified table names, you can work confidently with multiple databases and avoid common database-selection errors.

Learn more about database creation, SQL queries, and MySQL administration through the JustAcademy MySQL Training Course.

To explore the course and arrange a demonstration, visit Register for MySQL Course Demo.